﻿using System;
using System.IO;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Threading;
using ExcelRefresh.Com;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using Action = System.Action;
using MsoDocProperties = Microsoft.Office.Core.MsoDocProperties;

namespace ExcelRefresh.Excel
{
    public abstract class ExcelHelpers : ComHelpers
    {
        /// <summary>
        /// Opens an Excel workbook, and refreshes all data, including any Powerpivot cube.
        /// </summary>
        /// <param name="filePath">The absolute path to the Excel file to refresh</param>
        /// <remarks>
        /// If the workbook is already open by another user, this method will skip past it (behavior which might need rethinking).
        /// Otherwise, any embedded PowerPivot cube is detected and reprocessed, after which all data OLEDB and ODBC connections
        /// in the workbook are refreshed.
        /// </remarks>
        public static void RefreshAllWorkbookData(FileToRefresh file)
        {
            UsingWorkbook(file.FilePath, book => ProcessExcelWorkbook(file, book));
        }

        private static void ProcessExcelWorkbook(FileToRefresh file, Workbook book)
        {
            var startTime = DateTime.Now;

            if (book.ReadOnly)
            {
                Trace.TraceWarning("Skipping file (open by another user) : {0}", file.FilePath);
                throw new FileInUseException("Excel file in use");
            }

            try
            {
                Trace.TraceInformation("Refreshing {0}", book.Name);
                RefreshAll(book);
                var duration = DateTime.Now - startTime;
                Trace.TraceInformation("Refreshed {1}  - Duration: {0}", duration, file.FilePath);
                book.Comments = string.Format("Refreshed: {1:dd MMM, HH\\:mm}  Duration: {0:mm\\:ss}", duration, DateTime.Now);
                book.Subject = string.Format("Duration: {0:mm\\:ss}", duration);
                Trace.TraceInformation("Saving {0}", file.FilePath);
                book.Save();
                Trace.TraceInformation("Saved {0}", file.FilePath);
            }
            catch (System.Runtime.InteropServices.COMException cex)         
            {
                if (cex.Message.Contains("0x800A03EC"))      //  E_NAME_NOT_FOUND occurs here if too many concurrent PowerPivot files
                {
                    Trace.TraceWarning("Too Many PowerPivot files Open {0}", file.FilePath);
                    throw new TooManyPowerPivotFilesException();                // allow the scheduler to decide whether to re-queue the file
                }
                if(cex.Message.Contains("0x80010105"))
                {
                    Trace.TraceWarning("RPC Server Error 0x80010105. You may need to reduce concurrency on the Excel Refresher. File: {0}", file.FilePath);
                    book.Comments = "RPC Server Error 0x80010105. Probably too many parallel refreshes.";
                    book.Save();
                    throw;
                }
                
                Trace.TraceError("Error {1} : {0}", cex.Message, file.FilePath);
                book.Comments = string.Format("Refresh Error: {0}", cex.Message);
                book.Save();
                return;
            }
            catch (Exception ex)
            {
                Trace.TraceError("Error {1} : {0}", ex.Message, file.FilePath);
                book.Comments = string.Format("Refresh Error: {0}", ex.Message);
                book.Save();
                return;
            }

            try
            {
                if (file.BackupType != BackupStrategy.None)
                    CreateArchive(book, file.BackupType);

                var copyToFolder = Settings.CopyRefreshedFilesToFolder;
                if (!string.IsNullOrEmpty(copyToFolder) && !file.RootPath.StartsWith(copyToFolder))
                {
                    var newPath = file.FilePath.Replace(file.RootPath, copyToFolder);
                    book.SaveAs(newPath);
                    CreateArchive(book, file.BackupType);
                    Trace.TraceInformation("Copied {0}", file.FilePath);
                }
            }
            catch (Exception ex)
            {
                Trace.TraceError("Error Copying {0} : {1}", file.FilePath, ex.Message);
                throw;
            }
        }

        private static void CreateArchive(Workbook book, BackupStrategy strategy)
        {
            if (strategy == BackupStrategy.None)
                return;

            var filePath = book.FullName;
            var newName = filePath;

            switch (strategy)
            {
                case BackupStrategy.SameFolder:
                    newName = string.Format(
                            @"{0}\{1}.{3:yyyy\-MM\-dd HH\hmm}{2}",
                            Path.GetDirectoryName(filePath),
                            Path.GetFileNameWithoutExtension(filePath),
                            Path.GetExtension(filePath),
                            DateTime.Now);
                    break;

                case BackupStrategy.SubFolder:
                    var folder = string.Format(
                            @"{0}\{1}",
                            Path.GetDirectoryName(filePath),
                            Path.GetFileNameWithoutExtension(filePath));

                    Directory.CreateDirectory(folder);

                    newName = string.Format(
                            @"{0}\{1}\{1}.{3:yyyy\-MM\-dd HH\hmm}{2}",
                            Path.GetDirectoryName(filePath),
                            Path.GetFileNameWithoutExtension(filePath),
                            Path.GetExtension(filePath),
                            DateTime.Now);
                    break;
            }

            var statusProp = book.BuiltinDocumentProperties["Content Status"];
            var previousContentStatus = statusProp.Value;
            statusProp.Value = string.Empty;
            book.SaveCopyAs(newName);
            statusProp.Value = previousContentStatus;
            File.SetAttributes(newName, File.GetAttributes(newName) | FileAttributes.ReadOnly | FileAttributes.Archive | FileAttributes.Compressed);
        }

        public static void RefreshAll(Workbook workbook)
        {
            var restoreActions = new List<Action>();

            foreach (WorkbookConnection connection in workbook.Connections)
            {
                switch (connection.Type)
                {
                    case XlConnectionType.xlConnectionTypeOLEDB:
                        if (connection.Name == "ThisWorkbookDataModel") break;

                        if ( !connection.OLEDBConnection.SavePassword)
                            switch (connection.OLEDBConnection.CommandType)
                            {
                                case XlCmdType.xlCmdSql:
                                //case XlCmdType.xlCmdTableCollection:
                                case XlCmdType.xlCmdTable:
                                    string connectionString = connection.OLEDBConnection.Connection;
                                    var builder = new System.Data.OleDb.OleDbConnectionStringBuilder(connectionString);
                                    if (!builder.ContainsKey("Integrated Security")) 
                                        throw new Exception("Password required for connection " + connection.Name);
                                    break;
                            }

                        if (connection.InModel) break;
                        
                        if (!connection.OLEDBConnection.OLAP)
                        {
                            if (connection.OLEDBConnection.BackgroundQuery)
                                restoreActions.Add(() => connection.OLEDBConnection.BackgroundQuery = true);

                            connection.OLEDBConnection.BackgroundQuery = false;
                        }
                        break;

                    case XlConnectionType.xlConnectionTypeODBC:
                        if (connection.InModel) break;
                            
                        if (connection.ODBCConnection.BackgroundQuery)
                            restoreActions.Add(() => connection.ODBCConnection.BackgroundQuery = true);
                        connection.ODBCConnection.BackgroundQuery = false;
                        break;
                }
            }

            workbook.RefreshAll();  

            foreach (var action in restoreActions)
                action();
        }

    }
}




